package testing.domain.person.dao;

import testing.dataBase.DataBaseConnectionPool;
import testing.domain.person.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentDao {
    public int createStudent (Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        try {
            conn = DataBaseConnectionPool.getConnection();
            prepStat = conn.prepareStatement("INSERT INTO STUDENT (STUDENT_ID,NAME,SURNAME,PASSWORD,GRADE,GRYPA) VALUES (?, ?, ?, ?, ?, ?);");
            //
            ResultSet resultSet = prepStat.executeQuery("select * from STUDENT");
            int colCount = resultSet.getMetaData().getColumnCount();
            //
            colCount++;
            prepStat.setInt(1, colCount);
            prepStat.setString(2, student.getName());
            prepStat.setString(3, student.getSurname());
            prepStat.setString(4, student.getPassword());
            prepStat.setInt(5, student.getGrade());
            prepStat.setString(6, student.getGroup());

            return prepStat.executeUpdate();
        }finally {
            DataBaseConnectionPool.silentClose(prepStat);
            DataBaseConnectionPool.silentClose(conn);
        }
    }
    public java.util.Collection<Student> getAllStudent() throws SQLException {
        List<Student> list = new ArrayList<Student>();
        Connection conn = null;
        PreparedStatement prepStat = null;
        try {
            conn = DataBaseConnectionPool.getConnection();
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM STUDENT;");
            while(resultSet.next())
            {
                list.add(
                        new Student(
                            resultSet.getInt("STUDENT_ID"),
                            resultSet.getString("NAME"),
                            resultSet.getString("SURNAME"),
                            resultSet.getString("PASSWORD"),
                            resultSet.getString("GRYPA"),
                            resultSet.getInt("GRADE"))
                );
            }
            }finally {
            DataBaseConnectionPool.silentClose(prepStat);
            DataBaseConnectionPool.silentClose(conn);
        }
        return list;

    }
     public int editStudent(Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        try {
            conn = DataBaseConnectionPool.getConnection();
            Statement statement = conn.createStatement();
            prepStat = conn.prepareStatement("UPDATE STUDENT SET NAME = ?, SURNAME = ?, PASSWORD = ?, GRADE = ?, GRYPA = ? WHERE STUDENT_ID == ?;");
            prepStat.setString(1, student.getName());
            prepStat.setString(2, student.getSurname());
            prepStat.setString(3, student.getPassword());
            prepStat.setInt(4, student.getGrade());
            prepStat.setString(5, student.getGroup());
            prepStat.setInt(6, student.getStudentId());

            return prepStat.executeUpdate();
        }finally {
            DataBaseConnectionPool.silentClose(prepStat);
            DataBaseConnectionPool.silentClose(conn);
        }
    }
     public int deleteStudent(Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement prepStat = null;
        try {
            conn = DataBaseConnectionPool.getConnection();
            Statement statement = conn.createStatement();
            prepStat = conn.prepareStatement("DELETE FROM STUDENT WHERE STUDENT_ID = ?");
            prepStat.setInt(1, student.getStudentId());
            return prepStat.executeUpdate();
        }finally {
            DataBaseConnectionPool.silentClose(prepStat);
            DataBaseConnectionPool.silentClose(conn);
        }

     }
}
